package com.lb.trac.service;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import com.lb.trac.pojo.Evento;
import com.lb.trac.pojo.Progetti;
import com.lb.trac.pojo.StoricoTicket;
import com.lb.trac.pojo.Tickets;
import com.lb.trac.pojo.Utenti;

public class CustomQueryService {

	private JdbcTemplate jdbcTemplate;
	
	
	
	
	public void updateUtente(final Utenti[] utentis){
		String sql = "update UTENTI set password=?,nome=?,cognome=?,email=? where id_utente=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setString(1, utentis[idx].getPassword());
				pstmt.setString(2, utentis[idx].getNome());
				pstmt.setString(3, utentis[idx].getCognome());
				pstmt.setString(4, utentis[idx].getEmail());
				pstmt.setObject(5, utentis[idx].getIdUtente());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return utentis.length;
			}
		});
	}
	
	public void updateAndClosedTicket(final Tickets t,final StoricoTicket st ){
		String sql = "update tickets set id_tipo_intervento = ? where id_ticket=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, t.getIdTipoIntervento());
				pstmt.setObject(2, t.getIdTicket());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		sql = "update storico_ticket set fatturare=? where id_storico=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, st.getFatturare()?"1":"0");
				pstmt.setObject(2, st.getIdStorico());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		
	}
	

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void updateSocietaOnProgetto(final BigDecimal idProgetto,final BigDecimal idCliente) {
		String sql = "update progetti set id_cliente = ? where id_progetto=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1,idCliente);
				pstmt.setObject(2, idProgetto);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
	}

	
	
	public void deleteProject(final BigDecimal idProgetto) {
		String sql = "delete from UTENTE_PROGETTO where id_progetto=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, idProgetto);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		sql = "delete from progetti where id_progetto=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, idProgetto);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		
	}

	public void deleteSocieta(final int idSocieta) {
		String sql = "delete from SOCIETA where id_cliente=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, idSocieta);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
	
		
	}
	
	
/*	public void deleteProjectTemp(final Utenti u) {
		String sql = "delete from UTENTE_PROGETTO_TEMP where id_utente=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, u.getIdUtente());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		
		
	}*/
	
//Erika
/*	public void deleteProjectDaUtenteProgetto(final String idUtente) {
		String sql = " DELETE from UTENTE_PROGETTO  where id_progetto " +
					 " IN (SELECT id_progetto from UTENTE_PROGETTO_TEMP where id_utente=?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, idUtente);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		
		
	}*/
	
	public void updateStatoUtente(final Utenti u) {
		String sql = "update UTENTI set abilitato=? where id_utente=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setObject(1, u.getAbilitato());
				pstmt.setObject(2, u.getIdUtente());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
		
	}

	public void updateEventoInviato(final Evento evento) {
		String sql = "update EVENTO set inviato=1,DATA_INVIO=?,EMAIL_MESSAGE_ID=? where id_evento=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
				pstmt.setDate(1, new java.sql.Date(evento.getDataInvio().getTime()));
				pstmt.setString(2, evento.getEmailMessageId());
				pstmt.setObject(3, evento.getIdEvento());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return 1;
			}
		});
	}
		
		public void deleteEventoInviato(final String idEvento) {
			String sql = "delete from EVENTO  where id_evento=?";
			jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement pstmt, int idx) throws SQLException {
					pstmt.setObject(1, idEvento);
				}
				
				@Override
				public int getBatchSize() {
					// TODO Auto-generated method stub
					return 1;
				}
			});
			
	}
	
}
